package sqlmanager;

import java.sql.*;
import java.util.*;
import dialogs.Info;

public class SQLManager {
	private Connection conn = null;
	public Statement stmt = null;
	private DatabaseMetaData dbmd = null;
	static {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("����ODBC/JDBC�Ž����Ƿ������: ClassNotFoundException");
		}
	}

	public SQLManager() {
	}

	public void connectTo(Authentification auth) throws SQLException {
		if (null != conn)
			conn.close();
		conn = DriverManager.getConnection("jdbc:odbc:" + auth.source, auth.user, auth.password);
		stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		dbmd = conn.getMetaData();
	}

	public void close() {
		if (null == conn)
			return;
		try {
			this.conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("��ͼ�ر�����ʱ�����쳣");
		}
	}

	private List prepareList(List list) {
		if (null == list)
			list = new ArrayList();
		else
			list.clear();
		return list;
	}

	/*
	 * ȡ��Result Set�������ֶε���
	 */
	public List getFieldNames(ResultSet rs, List list) throws SQLException {
		this.prepareList(list);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		for (int i = 1; i <= columnCount; i++)
			list.add(rsmd.getColumnName(i));
		return list;
	}

	/*
	 * ȡ��ָ���ı�Ľṹ,field info
	 */
	private List getObjectList(ResultSet rs, List field) throws SQLException {
		this.prepareList(field);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		for (int i = 1; i <= columnCount; i++)
			field.add(new FieldInfo(rsmd.getColumnName(i), rsmd.getColumnTypeName(i), rsmd.getPrecision(i), rsmd.getScale(i),
					ResultSetMetaData.columnNoNulls == rsmd.isNullable(i), rsmd.isAutoIncrement(i)));
		return field;
	}

	/*
	 * ȡ�ñ�����ݣ�������ݺ��ֶ���
	 */
	public List getTableContents(ResultSet rs, List contents) throws SQLException {
		if (false == rs.isBeforeFirst())
			rs.beforeFirst();
		this.prepareList(contents);
		ArrayList row = null;
		int columnCount = rs.getMetaData().getColumnCount(), i = 1;
		while (rs.next()) {
			row = new ArrayList(columnCount);
			for (i = 1; i <= columnCount; i++)
				row.add(rs.getString(i));
			contents.add(row);
		}
		return contents;
	}

	/**
	 * ������public�������Ƕ���ӿ�
	 */
	public List getCatalogs() throws SQLException {
		ArrayList catalog = new ArrayList();
		ResultSet rs = this.dbmd.getCatalogs();
		while (rs.next())
			catalog.add(rs.getString(1));
		return catalog;
	}

	/*
	 * ��catalogָ����database��ȡ�ÿ��õı������ ���Բ���ִ��sql������ֱ��ȡ�ñ������
	 */
	public List getTableNames(String catalog) throws SQLException {
		ArrayList list = new ArrayList();
		ResultSet rs = dbmd.getTables(catalog, null, null, new String[] { "TABLE" });
		while (rs.next())
			list.add(rs.getString("TABLE_NAME"));
		return list;
	}

	private List getPrimaryKeys(String catalog, String table, List primaryKeys) throws SQLException {
		this.prepareList(primaryKeys);
		ResultSet rs = this.dbmd.getPrimaryKeys(catalog, null, table);
		while (rs.next())
			primaryKeys.add(rs.getString("COLUMN_NAME"));
		return primaryKeys;
	}

	public List getTableFields(String catalog, String table, List field, List primaryKeys) throws SQLException {
		return this.getObjectList(this.stmt.executeQuery("select * " + "from " + catalog + "." + table + " where 1=2 "), field);
	}

	public List getTableContents(String catalog, String table, List contents, List fieldInfos, List fieldNames, List primaryKeys) throws SQLException {
		ResultSet rs = stmt.executeQuery("select * from " + catalog + "." + table);
		this.getTableContents(rs, contents);
		this.getObjectList(rs, fieldInfos);
		this.getPrimaryKeys(catalog, table, primaryKeys);
		return this.getFieldNames(rs, fieldNames);
	}

	/*
	 * ִ�в�ѯ��Ȼ���÷��ص�resultset����model
	 */
	// public List execute( String sql )
	// throws SQLException
	// {
	// this.prepareList( topicTableModel.contents );
	// this.prepareList( topicTableModel.fieldNames );
	// ResultSet rs = this.stmt.executeQuery( sql );
	// ResultSetMetaData rsmd = rs.getMetaData();
	// int columnCount = rsmd.getColumnCount();
	// // get column names
	// for( int i = 1; i <= columnCount; i++ )
	// topicTableModel.fieldNames.add( rsmd.getColumnName( i ) );
	// // get content
	// ArrayList rowList = null;
	// while( rs.next() )
	// {
	// rowList = new ArrayList( columnCount );
	// for( int i = 1; i <= columnCount; i++ )
	// rowList.add( rs.getString( i ) );
	// topicTableModel.contents.add( rowList );
	// }
	// return topicTableModel.contents;
	// }
	public List getDBMSInfo(List infos) {
		this.prepareList(infos);
		try {
			infos.add(new Info("Product Name", dbmd.getDatabaseProductName()));
			infos.add(new Info("Product Version", dbmd.getDatabaseProductVersion()));
			infos.add(new Info("Driver Name", dbmd.getDriverName()));
			infos.add(new Info("Driver Version", dbmd.getDriverVersion()));
			infos.add(new Info("JDBC Version", dbmd.getJDBCMajorVersion() + "." + dbmd.getJDBCMinorVersion()));
			infos.add(new Info("Catalog Term", dbmd.getCatalogTerm()));
			infos.add(new Info("Catalog Separator", dbmd.getCatalogSeparator()));
			infos.add(new Info("Supports SQL92 Entry Level SQL", dbmd.supportsANSI92EntryLevelSQL() ? "Yes" : "No"));
			infos.add(new Info("Supports SQL92 Intermediate SQL", dbmd.supportsANSI92IntermediateSQL() ? "Yes" : "No"));
			infos.add(new Info("Supports SQL92 Full SQL", dbmd.supportsANSI92FullSQL() ? "Yes" : "No"));
			infos.add(new Info("Supports Transactions", dbmd.supportsTransactions() ? "Yes" : "No"));
			infos.add(new Info("Supports ALTER TABLE with Add Column", dbmd.supportsAlterTableWithAddColumn() ? "Yes" : "No"));
			infos.add(new Info("Supports ALTER TABLE with Drop Column", dbmd.supportsAlterTableWithDropColumn() ? "Yes" : "No"));
			infos.add(new Info("Supports Convert", dbmd.supportsConvert() ? "Yes" : "No"));
			infos.add(new Info("Max. Connection", String.valueOf(dbmd.getMaxConnections())));
			infos.add(new Info("Max. Statement Length", String.valueOf(dbmd.getMaxStatementLength())));
			infos.add(new Info("Max. Table Name Length", String.valueOf(dbmd.getMaxTableNameLength())));
			infos.add(new Info("Max. Column Name Length", String.valueOf(dbmd.getMaxColumnNameLength())));
			infos.add(new Info("Max. Column In Table", String.valueOf(dbmd.getMaxColumnsInTable())));
			infos.add(new Info("Max. Row Size", dbmd.getMaxRowSize() + "byte"));
			return infos;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("��ͼ��ȡ��ݿ���Ϣʱ�������: SQLManager.getDBMSInfo()");
			return infos;
		}
	}

	public List getDBMSDetail(List list) {
		this.prepareList(list);
		try {
			list.add(new Info("Key Words", dbmd.getSQLKeywords()));
			list.add(new Info("Numeric Functions", dbmd.getNumericFunctions()));
			list.add(new Info("String Functions", dbmd.getStringFunctions()));
			list.add(new Info("System Functions", dbmd.getSystemFunctions()));
			list.add(new Info("Time&Date Functions", dbmd.getTimeDateFunctions()));
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		}

	}

	public List getDBMSTypes(List topicList) {
		this.prepareList(topicList);
		int temp = 0;
		List aType = null;
		String typeName = null, tempValue = null;
		try {
			ResultSet rs = this.dbmd.getTypeInfo();
			while (rs.next()) {
				aType = new ArrayList();
				typeName = rs.getString("TYPE_NAME");
				aType.add(new Info("Type Name", typeName));

				temp = rs.getInt("DATA_TYPE");

				aType.add(new Info("JDBC Type Name", JDBCTypes.getJDBCTypeName(temp) + "  [code: " + temp + "]"));
				aType.add(new Info("Max. Precision", String.valueOf(rs.getInt("PRECISION"))));
				aType.add(new Info("Literal Prefix", rs.getString("LITERAL_PREFIX")));
				aType.add(new Info("Literal Suffix", rs.getString("LITERAL_SUFFIX")));
				aType.add(new Info("Create parameters", rs.getString("CREATE_PARAMS")));

				temp = rs.getShort("NULLABLE");
				if (DatabaseMetaData.typeNoNulls == temp)
					tempValue = "does not allow NULL values";
				else if (DatabaseMetaData.typeNullable == temp)
					tempValue = "allows NULL values";
				else if (DatabaseMetaData.typeNullableUnknown == temp)
					tempValue = "nullability unknown";
				aType.add(new Info("Nullable", tempValue));

				aType.add(new Info("Case sensitive", rs.getString("CASE_SENSITIVE")));

				temp = rs.getShort("SEARCHABLE");
				if (DatabaseMetaData.typePredNone == temp)
					tempValue = "No support WHERE";
				else if (DatabaseMetaData.typePredChar == temp)
					tempValue = "Only supported with WHERE .. LIKE";
				else if (DatabaseMetaData.typePredBasic == temp)
					tempValue = "Supported except for WHERE .. LIKE";
				else if (DatabaseMetaData.typeSearchable == temp)
					tempValue = "Supported for all WHERE ..";
				aType.add(new Info("Searchable", tempValue));

				aType.add(new Info("Unsigned", rs.getString("UNSIGNED_ATTRIBUTE")));
				aType.add(new Info("Fixed Point", rs.getString("FIXED_PREC_SCALE")));
				aType.add(new Info("Auto Increament", rs.getString("AUTO_INCREMENT")));
				aType.add(new Info("Local Type Name", rs.getString("LOCAL_TYPE_NAME")));
				aType.add(new Info("Min. Scale", String.valueOf(rs.getShort("MINIMUM_SCALE"))));
				aType.add(new Info("Max. Scale", String.valueOf(rs.getShort("MAXIMUM_SCALE"))));
				aType.add(new Info("Number Radix", String.valueOf(rs.getInt("NUM_PREC_RADIX"))));

				topicList.add(new Info(typeName, aType));
			}
			return topicList;
		} catch (SQLException e) {
			e.printStackTrace();
			return topicList;
		}

	}

	public List getDBMSTypeNames() {
		ArrayList list = new ArrayList();
		try {
			ResultSet rs = dbmd.getTypeInfo();
			while (rs.next())
				list.add(rs.getString("TYPE_NAME"));
			Collections.sort(list);
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			Collections.sort(list);
			return list;
		}
	}

	private String test() {
		StringBuffer buf = new StringBuffer("");
		try {
			ResultSet rs = dbmd.getTypeInfo();
			while (rs.next()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++)
					buf.append(rs.getString(i) + "  ");
				buf.append('\n');
			}
			return buf.toString();
		} catch (SQLException e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
			return buf.toString();
		}
	}
}
